package db.access;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.SortedSet;
import java.util.TreeSet;

import common.enums.AnswerEnum;
import common.enums.DateOfBirthEnum;
import common.enums.HeightEnum;
import common.enums.WeightEnum;
import common.interfaces.DBInterface;
import common.types.FictionalCharacter;
import common.types.GameInconsistentException;
import common.types.Pair;
import common.types.StatisticsEntry;
import common.utils.CommonUtils;

import db.freebase.FreeBaseDBImporter;
import db.manager.DBManager;
import db.utils.SQLUtils;



public class DBImpl implements DBInterface {

	private DBManager dbManager;

	public DBImpl() throws IOException, SQLException {
		this.dbManager = DBManager.getInstance();
		this.dbManager.init();
	}

	@Override
	public Pair<List<FictionalCharacter>, Integer> queryCharacter(String name, int limit) throws Exception {
		Connection conn = dbManager.poolConnection();
		Statement stmt;
		ResultSet rs;
		
		List<FictionalCharacter> resList = new ArrayList<FictionalCharacter>();
		
		stmt = conn.createStatement();
		Integer relSize;
		
		rs = stmt.executeQuery("SELECT COUNT(*) " +
								"FROM FictionalCharacter " +
								"WHERE characterName LIKE '%" + name + "%'");
		
		rs.next();
		relSize = rs.getInt(1);
		
		rs = stmt.executeQuery("SELECT characterID, gender, height, weight, characterName " +
								"FROM FictionalCharacter " +
								"WHERE characterName LIKE '%" + name + "%'" +
								"LIMIT " + relSize);

		while(rs.next() && (resList.size() < limit)){	
			FictionalCharacter fc = new FictionalCharacter(rs.getInt("characterID"), rs.getString("characterName"));
			resList.add(fc);
			
			fc.setGender(rs.getString("gender"));
			fc.setHeight(HeightEnum.fromString(rs.getString("height")));
			fc.setWeight(WeightEnum.fromString(rs.getString("weight")));			
		}
		
		if(resList.isEmpty()){
			return new Pair<List<FictionalCharacter>, Integer>(resList, relSize);
		}
		
		//based on
		updateFictionals(resList, "BasedOn", "CharacterBasedOn", "basedOnID", "basedOnID", "basedOnName");
		
		// place of birth
		updateFictionals(resList, "Places", "CharacterPlaceOfBirth", "placeID", "placeID", "placeName");
		
		//species
		updateFictionals(resList, "Species", "CharacterSpecies", "speciesID", "speciesID", "speciesName");
		
		//parents
		updateFictionals(resList, "FictionalCharacter", "CharacterParent", "characterID", "parentID", "characterName");
		
		//occupation
		updateFictionals(resList, "Occupation", "CharacterOccupation", "occupationID", "occupationID", "occupationName");
		
		//rank
		updateFictionals(resList, "Ranks", "CharacterRank", "rankID", "rankID", "rankName");
		
		//organizations
		updateFictionals(resList, "Organization", "CharacterOrganization", "organizationID", "organizationID", "organizationNAME");
		
		//powersOrAbilities
		updateFictionals(resList, "Powers", "CharacterPowers", "powerID", "powerID", "powerName");
		
		//appearsInTheseFictinalUniverses
		updateFictionals(resList, "Universe", "CharacterUniverses", "universeID", "universeID", "universeName");
		
		//children
		updateFictionals(resList, "FictionalCharacter", "CharacterChildren", "characterID", "childID", "characterName");
		
		//siblings
		updateFictionals(resList, "FictionalCharacter", "CharacterSibling", "characterID", "siblingID", "characterName");
		
		//marriedTo
		updateFictionals(resList, "FictionalCharacter", "CharacterMarriedTo", "characterID", "spouseID", "characterName");
		
		//characterCreatedBy
		updateFictionals(resList, "CreatedBy", "CharacterCreatedBy", "createdByID", "createdByID", "createdByName");
		
		//employers
		updateFictionals(resList, "Employer", "CharacterEmployer", "employerID", "employerID", "employerName");
		
		//quotations
		updateFictionals(resList, "Quotation", "CharacterQuotation", "quotationID", "quotationID", "quotationName");
		
		//romanticallyInvolvedWith
		updateFictionals(resList, "FictionalCharacter", "CharacterRomanticallyInvolvedWith", "characterID", "partnerID", "characterName");
		
		//ethnicity
		updateFictionals(resList, "Ethnicity", "CharacterEthnicity", "ethnicityID", "ethnicityID", "ethnicityName");
		
		//education
		updateFictionals(resList, "School", "CharacterSchool", "schoolID", "schoolID", "schoolName");
		
		//hasPossesed
		updateFictionals(resList, "Objects", "CharacterPossesedObject", "objectID", "objectID", "objectName");
		
		//medicalConditions
		updateFictionals(resList, "MedicalCondition", "CharacterMedicalCondition", "medicalConditionID", "medicalConditionID", "medicalConditionName");
		
		//dateOfBirth
		updateFictionals(resList, null, "CharacterDateOfBirth", null, null, null);
		
		// placesLived
		updateFictionals(resList, "Places", "CharacterPlacesLived", "placeID", "placeID", "placeName");
		
		rs.close();
		stmt.close();
		dbManager.returnConnection(conn);
		return new Pair<List<FictionalCharacter>, Integer>(resList, relSize);
	}

	@Override
	public boolean removeCharacter(int CID) throws Exception {
		Connection conn = dbManager.poolConnection();
		Statement stmt;
		int changedRows;
		boolean retVal = true;
		
		try{
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
	
			changedRows = stmt.executeUpdate("DELETE FROM FictionalCharacter WHERE characterID = " + CID);
			
			if(changedRows == 0){ 	//character wasn't found
				stmt.close();
				conn.setAutoCommit(true);
				dbManager.returnConnection(conn);
				return false;
			}
			
			conn.commit();			
			stmt.close();
		}
		catch (SQLException e){
			conn.rollback();
			retVal = false;
		}
		
		conn.setAutoCommit(true);
		dbManager.returnConnection(conn);
		
		return retVal;
	}

	@Override
	public boolean updateCharacter(FictionalCharacter character) throws Exception {
		Connection conn = dbManager.poolConnection();
		Statement stmt;
		ResultSet rs;
		boolean retVal = true;
		
		stmt = conn.createStatement();
		int ficID = character.getCharacterID();
		
		rs = stmt.executeQuery("SELECT characterName " +
								"FROM FictionalCharacter " +
								"WHERE characterID = " + ficID);
		
		if(!rs.next()){ // no such character
			rs.close();
			stmt.close();
			dbManager.returnConnection(conn);
			return false;
		} 
		
		try{
			conn.setAutoCommit(false);
			
			String height = (character.getHeight() == HeightEnum.NULL) ? "NULL" : "'" + character.getHeight() + "'";
			String weight = (character.getWeight() == WeightEnum.NULL) ? "NULL" : "'" + character.getWeight() + "'";
			String gender = (character.getGender() == null) ? "NULL" : "'" + character.getGender() + "'";
			
			stmt.executeUpdate("UPDATE FictionalCharacter " +
								"SET gender = " + gender + ", height = " + height + ", weight = " + weight + " " +
								"WHERE characterID = " + ficID);
			
			//date
			updateProperty(character, stmt, "CharacterDateOfBirth", null, null, null, null);
			
			//based on
			updateProperty(character, stmt, "CharacterBasedOn", "BasedOn", "basedOnID", "basedOnID", "basedOnName");
							
			//Quotation
			updateProperty(character, stmt, "CharacterQuotation", "Quotation", "quotationID", "quotationID", "quotationName");
			
			//Objects
			updateProperty(character, stmt, "CharacterPossesedObject", "Objects", "objectID", "objectID", "objectName");
			
			//Species
			updateProperty(character, stmt, "CharacterSpecies", "Species", "speciesID", "speciesID", "speciesName");
			
			//Occupation
			updateProperty(character, stmt, "CharacterOccupation", "Occupation", "occupationID", "occupationID", "occupationName");
			
			//Rank
			updateProperty(character, stmt, "CharacterRank", "Ranks", "rankID", "rankID", "rankName");
			
			//Organization
			updateProperty(character, stmt, "CharacterOrganization", "Organization", "organizationID", "organizationID", "organizationNAME");
			
			//Powers
			updateProperty(character, stmt, "CharacterPowers", "Powers", "powerID", "powerID", "powerName");
			
			//Universe
			updateProperty(character, stmt, "CharacterUniverses", "Universe", "universeID", "universeID", "universeName");
			
			//CreatedBy
			updateProperty(character, stmt, "CharacterCreatedBy", "CreatedBy", "createdByID", "createdByID", "createdByName");
			
			//Ethnicity
			updateProperty(character, stmt, "CharacterEthnicity", "Ethnicity", "ethnicityID", "ethnicityID", "ethnicityName");
			
			//School
			updateProperty(character, stmt, "CharacterSchool", "School", "schoolID", "schoolID", "schoolName");
			
			//MedicalCondition
			updateProperty(character, stmt, "CharacterMedicalCondition", "MedicalCondition", "medicalConditionID", "medicalConditionID", "medicalConditionName");
			
			//Places Lived
			updateProperty(character, stmt, "CharacterPlacesLived", "Places", "placeID", "placeID", "placeName");
			
			//Place Of Birth
			updateProperty(character, stmt, "CharacterPlaceOfBirth", "Places", "placeID", "placeID", "placeName");
			
			//Employer
			updateProperty(character, stmt, "CharacterEmployer", "Employer", "employerID", "employerID", "employerName");
			
			//Parent
			updateProperty(character, stmt, "CharacterParent", "FictionalCharacter", "characterID", "parentID", "characterName");
			
			//Children
			updateProperty(character, stmt, "CharacterChildren", "FictionalCharacter", "characterID", "childID", "characterName");
			
			//MarriedTo
			updateProperty(character, stmt, "CharacterMarriedTo", "FictionalCharacter", "characterID", "spouseID", "characterName");
			
			//RomanticallyInvolvedWith
			updateProperty(character, stmt, "CharacterRomanticallyInvolvedWith", "FictionalCharacter", "characterID", "partnerID", "characterName");
			
			//Sibling
			updateProperty(character, stmt, "CharacterSibling", "FictionalCharacter", "characterID", "siblingID", "characterName");
		
			
			conn.commit();
		}
		catch(SQLException e){	
			conn.rollback();
			e.printStackTrace();
			retVal = false;
		}
	
		conn.setAutoCommit(true);
		dbManager.returnConnection(conn);
		return retVal;

	}

	@Override
	public boolean createCharacter(FictionalCharacter character) throws Exception {

		Connection conn = dbManager.poolConnection();
		Statement stmt;
		ResultSet rs;
		boolean retVal = true;
		int charID;
			
		try{
			conn.setAutoCommit(false);
			stmt = conn.createStatement();

			String height = (character.getHeight() == HeightEnum.NULL) ? "NULL" : "'" + character.getHeight() + "'";
			String weight = (character.getWeight() == WeightEnum.NULL) ? "NULL" : "'" + character.getWeight() + "'";
			String gender = (character.getGender() == null) ? "NULL" : "'" + character.getGender() + "'";
			
			stmt.executeUpdate("INSERT INTO FictionalCharacter(characterName, gender, height, weight) " +
								"VALUES('" + character.getName() + "', " +
										gender + ", " +
										height + ", " +
										weight + ")");
			
			rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");			
			rs.next();
			charID = rs.getInt("LAST_INSERT_ID()");
			
			//date
			insertProps(character, charID, stmt, "CharacterDateOfBirth", null, null, null);
			
			//based on
			insertProps(character, charID, stmt, "CharacterBasedOn", "BasedOn", "basedOnID", "basedOnName");
			
			//Quotation
			insertProps(character, charID, stmt, "CharacterQuotation", "Quotation", "quotationID", "quotationName");
			
			//Objects
			insertProps(character, charID, stmt, "CharacterPossesedObject", "Objects", "objectID", "objectName");
			
			//Species
			insertProps(character, charID, stmt, "CharacterSpecies", "Species", "speciesID", "speciesName");
			
			//Occupation
			insertProps(character, charID, stmt, "CharacterOccupation", "Occupation", "occupationID", "occupationName");
			
			//Rank
			insertProps(character, charID, stmt, "CharacterRank", "Ranks", "rankID", "rankName");
			
			//Organization
			insertProps(character, charID, stmt, "CharacterOrganization", "Organization", "organizationID", "organizationNAME");
			
			//Powers
			insertProps(character, charID, stmt, "CharacterPowers", "Powers", "powerID", "powerName");
			
			//Universe
			insertProps(character, charID, stmt, "CharacterUniverses", "Universe", "universeID", "universeName");
			
			//CreatedBy
			insertProps(character, charID, stmt, "CharacterCreatedBy", "CreatedBy", "createdByID", "createdByName");
			
			//Ethnicity
			insertProps(character, charID, stmt, "CharacterEthnicity", "Ethnicity", "ethnicityID", "ethnicityName");
			
			//School
			insertProps(character, charID, stmt, "CharacterSchool", "School", "schoolID", "schoolName");
			
			//MedicalCondition
			insertProps(character, charID, stmt, "CharacterMedicalCondition", "MedicalCondition", "medicalConditionID", "medicalConditionName");
			
			//Places Lived
			insertProps(character, charID, stmt, "CharacterPlacesLived", "Places", "placeID", "placeName");
			
			//Place Of Birth
			insertProps(character, charID, stmt, "CharacterPlaceOfBirth", "Places", "placeID", "placeName");
			
			//Employer
			insertProps(character, charID, stmt, "CharacterEmployer", "Employer", "employerID", "employerName");
			
			//Parent
			insertProps(character, charID, stmt, "CharacterParent", "FictionalCharacter", "characterID", "characterName");
			
			//Children
			insertProps(character, charID, stmt, "CharacterChildren", "FictionalCharacter", "characterID", "characterName");
			
			//MarriedTo
			insertProps(character, charID, stmt, "CharacterMarriedTo", "FictionalCharacter", "characterID", "characterName");
			
			//RomanticallyInvolvedWith
			insertProps(character, charID, stmt, "CharacterRomanticallyInvolvedWith", "FictionalCharacter", "characterID", "characterName");
			
			//Sibling
			insertProps(character, charID, stmt, "CharacterSibling", "FictionalCharacter", "characterID", "characterName");
		
			conn.commit();
			rs.close();
			stmt.close();
		}
		catch (SQLException e){
			conn.rollback();
			e.printStackTrace();
			retVal = false;
		}
		
		conn.setAutoCommit(true);
		dbManager.returnConnection(conn);
		return retVal;
	}


	@Override
	public Pair<SortedSet<StatisticsEntry>, Integer> queryStatisticsHistory() throws Exception {
		
		Connection connection1=null, connection2=null;
		Statement statement=null, CharacterNameStatement=null;
		ResultSet		statisticsHistoryResultSet=null,CharacterNameResultSet=null,rs=null;
		
		StringBuilder sb;
		
	    sb = new StringBuilder();
	    sb.append(" SELECT * ");
	    sb.append(" FROM StatisticsHistory ");
	    String selectionString = sb.toString();
		
	    sb = new StringBuilder();
	    sb.append(" SELECT count(*) as totalCorrectGuesses ");
	    sb.append(" FROM StatisticsHistory ");
	    sb.append(" WHERE isCorrect=1 ");
	    String countString = sb.toString();	    
	    
		SortedSet<StatisticsEntry> entries = new TreeSet<StatisticsEntry>();
		Integer numCorrectGuesses=new Integer(-1);
//		Pair<SortedSet<StatisticsEntry>, Integer> pair=null;
		
		
		
		
		try {
			connection1 = dbManager.poolConnection();
			connection2 = dbManager.poolConnection();
			
			statement = connection1.createStatement();
			CharacterNameStatement = connection2.createStatement();
			statisticsHistoryResultSet	=	statement.executeQuery(selectionString);

			while (statisticsHistoryResultSet.next() == true)
			{
				
				String playersChoice = statisticsHistoryResultSet.getString("playersChoice");
				int CID = statisticsHistoryResultSet.getInt("CID");
				boolean isCorrect = statisticsHistoryResultSet.getBoolean("isCorrect");
				Timestamp gameTimeStamp = statisticsHistoryResultSet.getTimestamp("gameTimeStamp");
				
				String actualCharacterName=null;
				if (!isCorrect){
				
			    sb = new StringBuilder();
			    sb.append(" SELECT characterName ");
			    sb.append(" FROM FictionalCharacter ");
			    sb.append(" WHERE characterID=" + CID);
			    String nameByCIDString = sb.toString();	   
						
				CharacterNameResultSet	=	CharacterNameStatement.executeQuery(nameByCIDString);
				CharacterNameResultSet.next();
				actualCharacterName = CharacterNameResultSet.getString("characterName");
				} else if (isCorrect) {
					 actualCharacterName=playersChoice;
				}

				entries.add(new StatisticsEntry(actualCharacterName, playersChoice ,  isCorrect,  gameTimeStamp));
			}
			
			
			rs = statement.executeQuery(countString);
			rs.next();
			numCorrectGuesses=rs.getInt("totalCorrectGuesses");
		
		}
		catch (Exception e) {System.out.println(e.toString());}
		finally {
			if (CharacterNameResultSet!=null) CharacterNameResultSet.close();
			if (statisticsHistoryResultSet!=null) statisticsHistoryResultSet.close();
			if (rs!=null) rs.close();
			
			if (statement!=null) statement.close();
			if (CharacterNameStatement!=null) CharacterNameStatement.close();
			if (connection1!=null) { dbManager.returnConnection(connection1);}
			if (connection2!=null) { dbManager.returnConnection(connection2);}
		}

		return new Pair<SortedSet<StatisticsEntry>, Integer>(entries,numCorrectGuesses);

		
	}




	@Override
	public int getNewGameSessionID() throws SQLException  {
		Connection connection=null;
		Statement statement=null;
		ResultSet		rs=null;
		int				id=-1;
		int[] results;

		String sqlStatement=	 "INSERT INTO GameSessions(sessionStart)  VALUES(CURRENT_TIMESTAMP);  ";

		try { 
			connection = dbManager.poolConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();





			//LOCK GameSessions WRITE
			SQLUtils.executeUpdateAndVerify(statement, "LOCK TABLE GameSessions WRITE", 0) ;
			statement.executeUpdate(sqlStatement, 	new String[]{"sessionID"});
			rs		=	statement.getGeneratedKeys();
			rs.next();
			id		=	rs.getInt(1);
			//UNLOCK
			SQLUtils.executeUpdateAndVerify(statement, "UNLOCK TABLES;  ", 0) ;





			statement.addBatch("CREATE TABLE IF NOT EXISTS CountTable" + id + " (value varchar(1024)  NOT NULL,count INT UNSIGNED NOT NULL)ENGINE = MyISAM");
			statement.addBatch("CREATE TABLE IF NOT EXISTS MaxVsOthersTable" + id + " (value varchar(1024)  NOT NULL,count INT UNSIGNED  DEFAULT 0 )ENGINE = MyISAM");

			//LOCK TABLES
			statement.addBatch("LOCK TABLE PlayableCharacters READ, Properties READ, PropertiesStatus WRITE, GamesTable WRITE ");

			results = statement.executeBatch();

			for (int i=0; i<results.length; i++) {
				if (results[i] != 0) {
					throw new SQLException("Failed To Execute Batch ");
				}
			}		

			//createNewSession - duplicate all FC's into gamestable			
			sqlStatement = "INSERT INTO GamesTable(CID,sessionID)  ( SELECT characterID , " + id + " FROM PlayableCharacters) ";
			//can't know what should be returned (=number of rows manipulated)
			statement.executeUpdate(sqlStatement);


			sqlStatement = "select count(*) from Properties";
			rs	= statement.executeQuery(sqlStatement);
			rs.next();
			int numberOfProperties = rs.getInt(1);	

			sqlStatement = "INSERT INTO PropertiesStatus(SELECT " + id + ", PID , '',0 FROM Properties)";			
			SQLUtils.executeUpdateAndVerify(statement, sqlStatement, numberOfProperties) ;


			SQLUtils.executeUpdateAndVerify(statement, "UNLOCK TABLES;  ", 0) ;




			connection.commit();
			connection.setAutoCommit(true);
		}

		catch (SQLException e)
		{

			id=-1;

			try
			{
				connection.rollback();
			}
			catch (SQLException e2)
			{
				//failure to rollback
				throw new SQLException("RollbackFailure");
			}
		}




		finally {
			if (rs!=null) rs.close();
			if (statement!=null) statement.close();
			if (connection!=null) {connection.setAutoCommit(true);dbManager.returnConnection(connection);}
		}

		return id;
	}




	public void runAnalysisTest(int SID) throws SQLException  {
		Connection connection=null;
		Statement statement=null;
		try { 
			connection = dbManager.poolConnection();

			statement = connection.createStatement();		

//			statement.executeUpdate("CREATE INDEX genderIndex ON fictionalcharacter(gender)");
			
			//diversity 0		
//			performAnalysisOnProperty (statement, SID, false, null, "FictionalCharacter", null, "weight");
//			performAnalysisOnProperty (statement, SID, true, "Quotation", "CharacterQuotation", "quotationName", "quotationID");
//			performAnalysisOnProperty (statement, SID, true, "FictionalCharacter", "CharacterChildren", "characterName", "childID","characterID");
//			performAnalysisOnProperty (statement, SID, true, "CharacterDateOfBirth", "CharacterDateOfBirth", "standardDate", "standardDate");
			
			
			//diversity 1
			performAnalysisOnProperty (statement, SID, false, null, "FictionalCharacter", null, "gender");
			performAnalysisOnProperty (statement, SID, true, "CharacterDateOfBirth", "CharacterDateOfBirth", "standardDate", "standardDate");
			performAnalysisOnProperty (statement, SID, true, "BasedOn", "CharacterBasedOn", "basedOnName", "basedOnID");
			performAnalysisOnProperty (statement, SID, true, "FictionalCharacter", "CharacterParent", "characterName", "parentID","characterID");
			
		}
		finally {

			if (statement!=null) statement.close();
			if (connection!=null) dbManager.returnConnection(connection);
		}	
	}	


	
	


	@Override
	public void runAnalysis(int SID) throws SQLException  {
		Connection connection=null;
		Statement statement=null;



		try { 
			connection = dbManager.poolConnection();

			statement = connection.createStatement();		


			performAnalysisOnProperty (statement, SID, false, null, "FictionalCharacter", null, "gender");
			performAnalysisOnProperty (statement, SID, false, null, "FictionalCharacter", null, "height");
			performAnalysisOnProperty (statement, SID, false, null, "FictionalCharacter", null, "weight");

			performAnalysisOnProperty (statement, SID, true, "CharacterDateOfBirth", "CharacterDateOfBirth", "standardDate", "standardDate");
	
			
			performAnalysisOnProperty (statement, SID, true, "BasedOn", "CharacterBasedOn", "basedOnName", "basedOnID");
			performAnalysisOnProperty (statement, SID, true, "Quotation", "CharacterQuotation", "quotationName", "quotationID");
			performAnalysisOnProperty (statement, SID, true, "Objects", "CharacterPossesedObject", "objectName", "objectID");
			performAnalysisOnProperty (statement, SID, true, "Species", "CharacterSpecies", "speciesName", "speciesID");
			performAnalysisOnProperty (statement, SID, true, "Occupation", "CharacterOccupation", "occupationName", "occupationID");
			performAnalysisOnProperty (statement, SID, true, "Ranks", "CharacterRank", "rankName", "rankID");
			performAnalysisOnProperty (statement, SID, true, "Organization", "CharacterOrganization", "organizationName", "organizationID");
			performAnalysisOnProperty (statement, SID, true, "Powers", "CharacterPowers", "powerName", "powerID");
			performAnalysisOnProperty (statement, SID, true, "Universe", "CharacterUniverses", "universeName", "universeID");
			performAnalysisOnProperty (statement, SID, true, "CreatedBy", "CharacterCreatedBy", "createdByName", "createdByID");
			performAnalysisOnProperty (statement, SID, true, "Ethnicity", "CharacterEthnicity", "ethnicityName", "ethnicityID");

			performAnalysisOnProperty (statement, SID, true, "School", "CharacterSchool", "schoolName", "schoolID");		
			performAnalysisOnProperty (statement, SID, true, "MedicalCondition", "CharacterMedicalCondition", "medicalConditionName", "medicalConditionID");
			performAnalysisOnProperty (statement, SID, true, "Places", "CharacterPlacesLived", "placeName", "placeID");
			performAnalysisOnProperty (statement, SID, true, "Places", "CharacterPlaceOfBirth", "placeName", "placeID");
			performAnalysisOnProperty (statement, SID, true, "Employer", "CharacterEmployer", "employerName", "employerID");		

			performAnalysisOnProperty (statement, SID, true, "FictionalCharacter", "CharacterParent", "characterName", "parentID","characterID");
			performAnalysisOnProperty (statement, SID, true, "FictionalCharacter", "CharacterChildren", "characterName", "childID","characterID");
			performAnalysisOnProperty (statement, SID, true, "FictionalCharacter", "CharacterMarriedTo", "characterName", "spouseID","characterID");
			performAnalysisOnProperty (statement, SID, true, "FictionalCharacter", "CharacterRomanticallyInvolvedWith", "characterName", "partnerID","characterID");
			performAnalysisOnProperty (statement, SID, true, "FictionalCharacter", "CharacterSibling", "characterName", "siblingID","characterID");


		}


		finally {

			if (statement!=null) statement.close();
			if (connection!=null) dbManager.returnConnection(connection);
		}	



	}	






	//Overload
	private void performAnalysisOnProperty (
			Statement statement, 
			int SID, 
			boolean manyToMany,
			String propertyTableName,
			String relationshipTableName,
			String propertyName,
			String propertyID)
	throws SQLException{	
		performAnalysisOnProperty(statement,SID,manyToMany,propertyTableName,relationshipTableName,propertyName,propertyID,propertyID);
	}	



	private void performAnalysisOnProperty (
			Statement statement, 
			int SID, 
			boolean manyToMany,
			String propertyTableName,
			String relationshipTableName,
			String propertyName,
			String relationshipTableID,
			String propertyTableID)
	throws SQLException{


		String sqlString;
		ResultSet		rs=null;
		int PID=0;

		int bestCutCount = 0;


		


		try { 



			sqlString = "delete from CountTable" + SID;
			statement.executeUpdate(sqlString);
			sqlString = "delete from MaxVsOthersTable" + SID; 
			statement.executeUpdate(sqlString);


			
			if (manyToMany==false) {	
				//retrieve property's id by name
				sqlString = "SELECT PID FROM Properties WHERE  propertyName = '" + relationshipTableID + "'";
				rs	= statement.executeQuery(sqlString);
				rs.next();
				PID = rs.getInt("PID");		

				/* count the various populations within this property */
				sqlString = 
					"INSERT INTO CountTable" + SID + 
					" (SELECT " + relationshipTableID + ",  count(*) AS count" +
					" FROM " + relationshipTableName + " AS relationshipTable, GamesTable AS gt" +
					" WHERE relationshipTable.characterID= gt.CID AND sessionID = " + SID +
					" GROUP BY " + relationshipTableID +
					" HAVING " + relationshipTableID + " is not NULL)";

			} 			else if (manyToMany==true) {
				//retrieve property's id by name
				sqlString = "SELECT PID FROM Properties WHERE  propertyName = '" + relationshipTableName + "'";
				rs	= statement.executeQuery(sqlString);
				rs.next();
				PID = rs.getInt("PID");					


				/* count the various populations within this property */
				sqlString = 
					"INSERT INTO CountTable" + SID + 
					" (SELECT " + relationshipTableID + ",  count(*) AS count" +
					" FROM " + relationshipTableName + " AS propertyTable, GamesTable AS gt" +
					" WHERE propertyTable.CID=gt.CID AND sessionID = " + SID +
					" GROUP BY " + relationshipTableID +
					" HAVING " + relationshipTableID + " is not NULL)";
			}
			
			
			
			
				int PopulationsDiversity = statement.executeUpdate(sqlString);
//				might be 0 rows affected if the table count table is empty (if no populations in property)
				if (PopulationsDiversity>0){
			
				
				/* since there might be numerous tuples in the result (i.e. several values with the same count being equal to the maximal)
				 * they are grouped by their (single value )count, so the result contains a single tuple
				 * as to the value coupled with the count:
				* it is true that it can't be guaranteed which one will be returned, and it can be any value
				* but it doesn't matter - any one could do (as long as it's count is maximal, and it is)
				*/
				/* take the max population */
				
				
				if (manyToMany==false) {	
				sqlString = 
					"INSERT INTO MaxVsOthersTable" + SID + 
					"( SELECT value, count" + 
					" FROM CountTable" + SID + 
					" WHERE count = (SELECT DISTINCT  max(count) from CountTable" + SID + ")" +
					" GROUP BY count)";
				} 			
				else if (manyToMany==true) {

				sqlString = 
					"INSERT INTO MaxVsOthersTable" + SID + 
					"( SELECT DISTINCT propertyTable." + propertyName + ", count" + 
					" FROM CountTable" + SID + "," + propertyTableName + " AS propertyTable" + 
					" WHERE count = (SELECT DISTINCT  max(count) FROM CountTable" + SID + ")" +
					" AND propertyTable." + propertyTableID + "= CountTable" + SID + ".value" +
					" GROUP BY count)";					
			}



			statement.executeUpdate(sqlString);



			sqlString = "SELECT value FROM MaxVsOthersTable" + SID;

			rs	= statement.executeQuery(sqlString);
			rs.next();
			String bestCutPopulation = rs.getString("value");				












			/* take the sum of others different from the maximal */
			if (manyToMany==false) {						
				sqlString = 

					"( SELECT sum(count) AS count" + 
					" FROM    CountTable" + SID + 
					" WHERE CountTable" + SID + ".value != (SELECT value FROM MaxVsOthersTable" + SID + "))";
			}
			else if (manyToMany==true) {
				sqlString = 
					"( SELECT sum(count) AS count" + 
					" FROM    CountTable" + SID + 
					" WHERE CountTable" + SID + ".value != " +
					"(SELECT " + propertyTableID + " " +
					"FROM " + propertyTableName + " AS property, MaxVsOthersTable" + SID + " " +
					"WHERE property." + propertyName + " = MaxVsOthersTable"+SID+".value" +
					" GROUP BY " + propertyName + "))";		
			}
			
			
			
			rs = statement.executeQuery(sqlString);
			rs.next();
			int othersCount = rs.getInt("count");

			
			sqlString = 
				"INSERT INTO MaxVsOthersTable" + SID +" VALUES(" +  
				"'others', " +othersCount + ")";
			
			SQLUtils.executeUpdateAndVerify(statement, sqlString, 1) ;


			String minimalPopulationSqlString =
				"SELECT count		FROM MaxVsOthersTable" + SID +		" WHERE count = (SELECT DISTINCT  min(count) from MaxVsOthersTable" + SID + ")";

			rs	= statement.executeQuery(minimalPopulationSqlString);
			rs.next();
			bestCutCount = rs.getInt("count");

			sqlString = 
				"UPDATE PropertiesStatus" +
				" SET bestCutPopulation='" + bestCutPopulation + "', bestCutCount=" + bestCutCount + 
				" WHERE sessionID=" + SID + " AND PID=" +PID; 				

				} else if (PopulationsDiversity==0){
//					sqlString = 
//						" DELETE " +
//						" FROM PropertiesStatus" +
//						" WHERE sessionID=" + SID + " AND PID=" +PID; 	
					
					sqlString = 
						"UPDATE PropertiesStatus" +
						" SET bestCutCount=" + bestCutCount + 
						" WHERE sessionID=" + SID + " AND PID=" +PID; 	
				}


			SQLUtils.executeUpdateAndVerify(statement, sqlString, 1) ;
				

		}




		finally {
			if (rs!=null) rs.close();
		}


	}
	


	
	
	

	@Override
	public String getQuestionTemplate(int PID) throws Exception {
	    Connection connection = dbManager.poolConnection();
	    Statement statement = null;
	    ResultSet rs = null;
	    
	    StringBuilder sb = new StringBuilder();
	    sb.append("SELECT questionTemplate ");
	    sb.append("FROM Properties ");
	    sb.append("WHERE PID = " + PID);
	    String sql = sb.toString();
	    
	    try {       
	        statement = connection.createStatement();
	        rs = statement.executeQuery(sql);     
	        if (rs.next()) {
	    	    String questionTemplate = rs.getString(1);
	    	    return questionTemplate;
	        } else {
	        	throw new GameInconsistentException("Requested question template wasn't found");
	        }
	        
	    } finally {
	        if (rs != null) {
	            rs.close();
	        }
	        if (statement != null) {
	            statement.close();
	        }
	        dbManager.returnConnection(connection);
	    }	    
	}

	@Override
	public void makeCut(int SID, int PID, AnswerEnum answer) throws Exception {
		if (answer.equals(AnswerEnum.DONT_KNOW)) {
			throw new GameInconsistentException("Can't filter guesses using given answer");
		}
		
        Connection connection = dbManager.poolConnection();
        Statement statement1 = null;
        Statement statement2 = null;
        ResultSet rs = null;
        
        try {
        	StringBuilder sb = new StringBuilder();
     	    sb.append("SELECT propertyName ");
     	    sb.append("FROM Properties ");
     	    sb.append("WHERE PID = " + PID);
     	    String sql = sb.toString();
     	    
     	    String location;
     	   statement1 = connection.createStatement();
	        rs = statement1.executeQuery(sql);     
	        if (rs.next()) {
	        	location = rs.getString(1);
	        } else {
	        	throw new GameInconsistentException("Failed to filter guesses using given property");
	        } 
	        
	        String operator = answer.equals(AnswerEnum.YES) ? "<>" : "="; 
	        PropertyLocationEnum propertyLocation = PropertyLocationEnum.get(location);
	        String holdingTable = propertyLocation.getHoldingTable();
	        String mediatorTable = propertyLocation.getMediatorTable();
	        String holderPropertyID = propertyLocation.getHolderPropertyID();
	        String mediatorPropertyID = propertyLocation.getMediatorPropertyID();
	        String propertyName = propertyLocation.getPropertyName();
	        
	        sb = new StringBuilder();
			sb.append("SELECT CID ");
			if (propertyLocation.hasMediatorTable()) {
				sb.append("FROM " + holdingTable + " , " + mediatorTable + " , PropertiesStatus ");
			    sb.append("WHERE GamesTable.CID = " + mediatorTable + ".CID");
				sb.append(" AND " + mediatorTable + "." + mediatorPropertyID + " = " + holdingTable + "." + holderPropertyID);
			} else {		
				sb.append("FROM " + holdingTable + " , PropertiesStatus ");
				sb.append("WHERE GamesTable.CID = " + holdingTable + "." + holderPropertyID);
			}
		    sb.append(" AND " +  holdingTable + "." + propertyName + " " + operator + " PropertiesStatus.bestCutPopulation"); 
		    sb.append(" AND ISNULL(" +  holdingTable + "." + propertyName + ") = FALSE");	
			sb.append(" AND GamesTable.sessionID = " + SID);
		    sb.append(" AND PropertiesStatus.PID = " + PID);
		    sb.append(" AND PropertiesStatus.sessionID = " + SID);
			String innerSql = sb.toString();
	        
		    sb = new StringBuilder();
		    sb.append("DELETE ");
		    sb.append("FROM GamesTable ");
		    sb.append("WHERE sessionID = " + SID); // No need to check SID again but I think it's better for optimization
		    sb.append(" AND CID IN (" + innerSql + ")"); // The inner query is independent so the compiler calculates the inner query only once
		    sql = sb.toString();
        
		    statement2 = connection.createStatement();
		    statement2.executeUpdate(sql);    
            // Rollback is not needed because of our Session mechanism
            
        } finally {
	        if (rs != null) {
	            rs.close();
	        }
            if (statement1 != null) {
                statement1.close();
            }
            if (statement2 != null) {
            	statement2.close();
            }
            dbManager.returnConnection(connection);
        }
	}

	@Override
	public boolean isGuessReady(int SID) throws Exception {
	    Connection connection = dbManager.poolConnection();
	    Statement statement = null;
	    ResultSet rs = null;
	    
	    StringBuilder sb = new StringBuilder();
	    sb.append("SELECT COUNT(*) ");
	    sb.append("FROM GamesTable ");
	    sb.append("WHERE sessionID = " + SID);
	    String sql = sb.toString();
	    
	    try {       
	        statement = connection.createStatement();
	        rs = statement.executeQuery(sql);     
	        if (rs.next()) {
	        	int count = rs.getInt(1);
	        	if (count == 1) {
	        		return true;
	        	} else if (count > 1){
	        		return false;
	        	}
	        }     	
	        throw new GameInconsistentException("No available guesses in database for this session");
	        
	    } finally {
	        if (rs != null) {
	            rs.close();
	        }
	        if (statement != null) {
	            statement.close();
	        }
	        dbManager.returnConnection(connection);
	    }	
	}
	
	@Override
	public Pair<String, Integer> getBestCut(int SID, int index) throws Exception {

	    if (index < 1) {
	        throw new GameInconsistentException("Illegal index value was passed");
	    }
	    
	    Connection connection = dbManager.poolConnection();
	    Statement statement = null;
	    ResultSet rs = null;
	    
	    StringBuilder sb = new StringBuilder();
	    sb.append("SELECT bestCutPopulation, PID FROM PropertiesStatus WHERE sessionID = ");
	    sb.append(String.valueOf(SID));
	    sb.append(" ORDER BY bestCutCount DESC");
	    sb.append(" LIMIT ");
	    sb.append(String.valueOf(index-1));
	    sb.append(", 1");
	    String sql = sb.toString();
	    
	    try {
	        
	        statement = connection.createStatement();
	        rs = statement.executeQuery(sql);
	        
	        if (rs.next()) {
	            String bestCutPopulation = rs.getString(1);
	            int bestCutPID = rs.getInt(2);
	            return new Pair<String, Integer>(bestCutPopulation, bestCutPID);
	        } else {
	            throw new GameInconsistentException("There seems to be a problem with the database");
	        }
	        
	    } finally {
	        if (rs != null) {
	            rs.close();
	        }
	        if (statement != null) {
	            statement.close();
	        }
	        dbManager.returnConnection(connection);
	    }
	    
	}
	
	@Override
	public Pair<Integer, String> getBestGuess(int SID) throws Exception {
	    
	        Connection connection = dbManager.poolConnection();
	        Statement statement = null;
	        ResultSet rs = null;
	        StringBuilder sb = new StringBuilder();
	        
	        try {
	            
	            int cid = -1;
	            String characterName = null;
	            
	            // First trying to merge with statistics to get a somewhat reasonable guess (as much as possible)
	            // we avoid left joining right away as we will lose information regarding 
	            // who appeared in StatisticsHistory and who hasen't (as those will be counted as 1, even though
	            // they did not appear in StatisticsHistory and this is the only information we can retain after 
	            // a count
	            // we simply pick the first choice if a result if found
	            sb.append("SELECT FictionalCharacter.characterName, GamesTable.CID, COUNT(*) AS cidCount ")
	              .append(" FROM FictionalCharacter, GamesTable, StatisticsHistory")
	              .append(" WHERE FictionalCharacter.characterID = GamesTable.CID AND")
	              .append(" GamesTable.CID = StatisticsHistory.CID AND")
	              .append(" GamesTable.sessionID = " + SID + " AND")
	              .append(" StatisticsHistory.isCorrect = 1")
	              .append(" GROUP BY GamesTable.CID")
	              .append(" HAVING cidCount >= 5")
	              .append(" ORDER BY cidCount DESC")
	              .append(" LIMIT 1");
	            String sql = sb.toString();
	            
	            statement = connection.createStatement();
	            rs = statement.executeQuery(sql);
	            
	            if (rs.next()) {
	                characterName = rs.getString(1);
	                cid = rs.getInt(2);
	                return new Pair<Integer, String>(cid, characterName);
	            } 
	            
	            rs.close();
	            
	            sb.setLength(0);
	            
	            // second approach
	            
	            sb.append("SELECT FictionalCharacter.characterName, FictionalCharacter.characterID, COUNT(*) AS cidCount ")
	            .append(" FROM FictionalCharacter, GamesTable, MostAttributesCharacters ")
	            .append(" WHERE FictionalCharacter.characterID = GamesTable.CID AND")
	            .append(" GamesTable.CID = MostAttributesCharacters.characterID AND")
	            .append(" GamesTable.sessionID = " + SID)
	            .append(" GROUP BY FictionalCharacter.characterID")
	            .append(" ORDER BY cidCount DESC")
	            .append(" LIMIT 1");
	            sql = sb.toString();
	            
	            rs = statement.executeQuery(sql);
	            
	            if (rs.next()) {
	                characterName = rs.getString(1);
	                cid = rs.getInt(2);
	                return new Pair<Integer, String>(cid, characterName);
	            } 
	            
	            rs.close();
	            
	            sb.setLength(0);

	            // falling back to a simple "pick the first one possible" approach
                sb.append("SELECT FictionalCharacter.characterName, GamesTable.CID")
                  .append(" FROM FictionalCharacter, GamesTable")
                  .append(" WHERE FictionalCharacter.characterID = GamesTable.CID AND")
                  .append(" GamesTable.sessionID = " + SID)
                  .append(" LIMIT 1");
                sql = sb.toString();
    
                rs = statement.executeQuery(sql);
    
                if (rs.next()) {
                    characterName = rs.getString(1);
                    cid = rs.getInt(2);
                    return new Pair<Integer, String>(cid, characterName);
                } else {
                    throw new GameInconsistentException("There seems to be something wrong with the database");
                }
	        } finally {
	            if (rs != null) {
	                rs.close();
	            }
	            if (statement != null) {
	                statement.close();
	            }
	            dbManager.returnConnection(connection);
	        }
	}

	@Override
	public void submitActual(int CID, boolean isCorrect, String actual) throws Exception {
	    Connection connection = dbManager.poolConnection();
	    Statement statement = null;
	    try {
	        statement = connection.createStatement();

	        String template = "INSERT INTO StatisticsHistory(playersChoice,CID,isCorrect,gameTimeStamp) ";
    	    template += "VALUES({0},{1},{2},{3})";
    	    
    	    String sql = MessageFormat.format(template, 
    	        CommonUtils.wrapStringWithQuotes(actual),
    	        String.valueOf(CID),
    	        isCorrect ? "1" : "0",
    	        "CURRENT_TIMESTAMP"
    	    );
    	    statement.executeUpdate(sql);
	    } finally {
	        if (statement != null) {
	            statement.close();
	        }
	        dbManager.returnConnection(connection);
	    }
	}
	
    @Override
    public int getNewGUISessionID() throws Exception {
        Connection connection = dbManager.poolConnection();
        Statement statement = null;
        ResultSet rs = null;
        int result = -1;
        try {
            statement = connection.createStatement();

            String sql = "SELECT COUNT(*) FROM GUISessions WHERE editingSession = 1";
            rs = statement.executeQuery(sql);
            
            boolean readAllowed = false;
            if (rs.next()) {
                readAllowed = rs.getInt(1) == 0;
            } else {
                throw new GameInconsistentException("There seems to be a problem with the database");
            }
            rs.close();
            
            if (!readAllowed) {
                return -1;
            }
            
            sql = "INSERT INTO GUISessions(sessionStart) VALUES(CURRENT_TIMESTAMP)";
            statement.executeUpdate(sql, new String[]{"sessionID"});
            rs = statement.getGeneratedKeys();
            if (rs.next()) {
                result = rs.getInt(1);
            } else {
                throw new GameInconsistentException("There seems to be a problem with the database");
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (statement != null) {
                statement.close();
            }
            dbManager.returnConnection(connection);
        }
        
        return result;
    }

    @Override
    public void cleanGUISession(int majorSID) throws SQLException {
        Connection connection = dbManager.poolConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            String sql = "DELETE FROM GUISessions WHERE sessionID = " + majorSID;
            statement.executeUpdate(sql);
        } finally {
            if (statement != null) {
                statement.close();
            }
            dbManager.returnConnection(connection);
        }
    }

    @Override
    public boolean requestEditMode(int majorSID) throws Exception {
        Connection connection = dbManager.poolConnection();
        Statement statement = null;
        ResultSet rs = null;
        try {
            
            statement = connection.createStatement();
            
            String sql = "SELECT COUNT(*) FROM GUISessions WHERE sessionID != " + majorSID;
            rs = statement.executeQuery(sql);
            
            boolean editAllowed = false;
            if (rs.next()) {
                editAllowed = rs.getInt(1) == 0;
            } else {
                throw new GameInconsistentException("There seems to be a problem with the database");
            }
            rs.close();
            
            if (editAllowed) {
                sql = "UPDATE GUISessions SET editingSession = 1 WHERE sessionID = " + majorSID;
                statement.executeUpdate(sql);
                return true;
            } else {
                return false;
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (statement != null) {
                statement.close();
            }
            dbManager.returnConnection(connection);
        }
    }
   
    @Override
    public void endEditMode(int majorSID) throws SQLException {
        Connection connection = dbManager.poolConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            String sql = "UPDATE GUISessions SET editingSession = 0 WHERE sessionID = " + majorSID;
            statement.executeUpdate(sql);
        } finally {
            if (statement != null) {
                statement.close();
            }
            dbManager.returnConnection(connection);
        }
    }

    @Override
    public void forceGuiSessionCleanup(int guiSessionID) throws Exception {
        Connection connection = dbManager.poolConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            String sql = "DELETE FROM GUISessions ";
            if (guiSessionID != -1) {
                sql += "WHERE sessionID != " + guiSessionID;
            }
            statement.executeUpdate(sql);
        } finally {
            if (statement != null) {
                statement.close();
            }
            dbManager.returnConnection(connection);
        }
    }
    
    @Override
    public boolean importToDB(File freeBaseRoot) throws Exception {
        String sql;
        Connection connection = dbManager.poolConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            sql = "CREATE UNIQUE INDEX midIndex ON FictionalCharacter(freeBaseMID)";
            statement.executeUpdate(sql);
            FreeBaseDBImporter.importToDB(freeBaseRoot);
            sql = "DROP INDEX midIndex ON FictionalCharacter";
            statement.executeUpdate(sql);
            return true;
        } catch (Exception e) {
            throw new GameInconsistentException("Import operation failed, database may very well be in an inconsistent state");
        } finally {
            if (statement != null) {
                statement.close();
            }
            dbManager.returnConnection(connection);
        }
    }

	@Override
	public void cleanGameSession(int SID) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;

		int[] results;					

		String sqlStatement;

		try { 
			connection = dbManager.poolConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();

			//NOTICE: MAKE SURE THESE DON'T BREAK ANY FOREIGN KEY CONSTRAINTS, OTHERWISE, THEY'D BE REJECTED
			//could not use prepared statment here, since setString converts the value to an SQL VARCHAR!!! which cannot be a tablename

			statement.addBatch("DROP TABLE IF EXISTS CountTable" + SID);
			statement.addBatch("DROP TABLE IF EXISTS MaxVsOthersTable" + SID);

			results = statement.executeBatch();

			for (int i=0; i<results.length; i++) {
				if (results[i] != 0) {
					throw new SQLException("Failed To Execute Batch ");
				}
			}						

			//can't know what should be returned (=number of rows manipulated)
			sqlStatement = "DELETE FROM GamesTable WHERE sessionID=" + SID;
			statement.executeUpdate(sqlStatement);
			sqlStatement = "DELETE FROM PropertiesStatus WHERE sessionID=" + SID;
			statement.executeUpdate(sqlStatement);					
			sqlStatement = "DELETE FROM GameSessions WHERE sessionID=" + SID;
			statement.executeUpdate(sqlStatement);

			connection.commit();
			connection.setAutoCommit(true);
		}
		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e2) {
				//failure to rollback
				throw new SQLException("RollbackFailure");
			}
		} finally {
			if (rs != null) {
			    rs.close();
			}
			if (statement != null) {
			    statement.close();
			}
			if (connection != null) {
			    connection.setAutoCommit(true); 
			    dbManager.returnConnection(connection);
			}
		}
	}	
	
	public boolean isCleanDB() throws Exception {
	    Connection connection = dbManager.poolConnection();
	    Connection connection2 = dbManager.poolConnection();
        Statement statement1 = null;
        Statement statement2 = null;
	    ResultSet rs1 = null;
	    ResultSet rs2 = null;
	    
	    StringBuilder sb = new StringBuilder();
	    sb.append("SELECT * ");
	    sb.append("FROM GameSessions ");
	    sb.append("WHERE sessionStart < (TIMESTAMPADD(HOUR,-2,CURRENT_TIMESTAMP())) ");
	    sb.append("LIMIT 1");
	    String sql1 = sb.toString();
	    
	    sb = new StringBuilder();
	    sb.append("SELECT * ");
	    sb.append("FROM GUISessions ");
	    sb.append("WHERE sessionStart < (TIMESTAMPADD(HOUR,-23,CURRENT_TIMESTAMP())) ");
	    sb.append("LIMIT 1");
	    String sql2 = sb.toString();
	    
	    try {       
	        statement1 = connection.createStatement();
	        rs1 = statement1.executeQuery(sql1); 
	        boolean isClean = !rs1.next();
	        if (isClean) {
		        statement2 = connection2.createStatement();
		        rs2 = statement2.executeQuery(sql2); 
		        isClean = !rs2.next();
	        }
	        return isClean;
	    } finally {
	        if (rs1 != null) {
	            rs1.close();
	        }
	        if (rs2 != null) {
	        	rs2.close();
	        }
	        if (statement1 != null) {
	            statement1.close();
	        }
	        if (statement2 != null) {
	        	statement2.close();
	        }
	        dbManager.returnConnection(connection);
	        dbManager.returnConnection(connection2);
	    }	
	}

	@Override
	public void garbageCollection() throws Exception {
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		Set<Integer> staleGameSessionIDs = new HashSet<Integer>();
		Set<Integer> staleGUISessionIDs = new HashSet<Integer>();
		int id;

		try { 
			connection = dbManager.poolConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery("SELECT * FROM GameSessions WHERE sessionStart < (TIMESTAMPADD(HOUR,-2,CURRENT_TIMESTAMP()))");
			while (rs.next()) {
				id = rs.getInt("sessionID");
				staleGameSessionIDs.add(id);
			}
			
			for (int i: staleGameSessionIDs) {
				cleanGameSession(i);
			}
			
			rs = statement.executeQuery("SELECT * FROM GUISessions WHERE sessionStart < (TIMESTAMPADD(HOUR,-23,CURRENT_TIMESTAMP()))");
			while (rs.next()) {
				id = rs.getInt("sessionID");
				staleGUISessionIDs.add(id);
			}
			
			for (int i: staleGUISessionIDs) {
				cleanGUISession(i);
			}		
		} finally {
			if (rs != null) {
			    rs.close();
			}
			if (statement != null) {
			    statement.close();
			}
			if (connection != null) {
			    dbManager.returnConnection(connection);
			}
		}
	}
	
	private void updateProperty(FictionalCharacter character, Statement stmt, String charRel, 
			String relation, String IDProp, String charIDProp, String pName) throws SQLException {
		
		int ficID = character.getCharacterID();
		ResultSet rs;
		
		//date 		=====================================
		if(charRel.equalsIgnoreCase("CharacterDateOfBirth")){
			
			rs = stmt.executeQuery("SELECT characterDateOfBirthID, standardDate, otherDate "+
									"FROM CharacterDateOfBirth " +
									"WHERE CID = " + ficID);
			
			//result set to map
			Map<Pair<DateOfBirthEnum,String>, Integer> propMap = new HashMap<Pair<DateOfBirthEnum,String>, Integer>();
			while(rs.next()){
				
				Pair<DateOfBirthEnum,String> pair = new Pair<DateOfBirthEnum,String>
								(DateOfBirthEnum.fromString(rs.getString("standardDate")), rs.getString("otherDate"));
				
				propMap.put(pair, rs.getInt("characterDateOfBirthID"));
			}
			
			//deleting from date table
			for(Pair<DateOfBirthEnum,String> datePair : propMap.keySet()){
				if(!character.getDateOfBirth().contains(datePair)){

					stmt.executeUpdate("DELETE FROM CharacterDateOfBirth " +
										"WHERE characterDateOfBirthID = " + propMap.get(datePair));
				}
			}
			
			//adding to a property
			FictionalCharacter fc = new FictionalCharacter();
			Set<Pair<DateOfBirthEnum,String>> propSet = new HashSet<Pair<DateOfBirthEnum,String>>();
			
			for(Pair<DateOfBirthEnum,String> datePair : character.getDateOfBirth()){
				if(!propMap.keySet().contains(datePair)){				
					propSet.add(datePair);
				}
			}
			
			fc.setDateOfBirth(propSet);			
			insertProps(fc, ficID, stmt, "CharacterDateOfBirth", null, null, null);
			
			return;
		}
		
		//employer 		================================
		
		if(charRel.equalsIgnoreCase("CharacterEmployer")){
			
			rs = stmt.executeQuery("SELECT characterEmployerID, employerName, employmentTitle "+
									"FROM Employer, CharacterEmployer " +
									"WHERE Employer.employerID = CharacterEmployer.employerID " +
											"AND CID = " + ficID);
			
			//result set to map
			Map<String, Integer> propMap = new HashMap<String, Integer>();
			while(rs.next()){
				
				String empName = rs.getString("employerName");
				String empTitle = rs.getString("employmentTitle");
				String empFull = ((empTitle == null) || (empTitle.isEmpty()) ? empName : (empName + ":" + empTitle));
				
				propMap.put(empFull, rs.getInt("characterEmployerID"));
			}
			
			//deleting from employer
			for(String name : propMap.keySet()){
				if(!character.get(charRel).contains(name)){

					stmt.executeUpdate("DELETE FROM CharacterEmployer " +
										"WHERE characterEmployerID = " + propMap.get(name));
				}
			}
			
			//adding to a property
			Set<String> nameSet = new HashSet<String>();
			
			for(String name : character.get(charRel)){
				if(!propMap.keySet().contains(name)){
					nameSet.add(name);
				}
			}
			
			insertProps(nameSet, ficID, stmt, charRel, relation, IDProp, pName);
			
			return;			
		}
		
		
		
		// rest of properties - not date or employer ==================================
		
		rs = stmt.executeQuery("SELECT " + relation + "." + IDProp + ", " + pName + " "+
								"FROM " + relation + ", " + charRel + " " +
								"WHERE " + relation + "." + IDProp + " = " + charRel + "." + charIDProp + " " +
										"AND CID = " + ficID);
		
		//result set to map
		Map<String, Integer> propMap = new HashMap<String, Integer>();
		while(rs.next()){
			propMap.put(rs.getString(pName), rs.getInt(relation + "." + IDProp));
		}
		
		//deleting from a property
		for(String name : propMap.keySet()){
			if(!character.get(charRel).contains(name)){

				stmt.executeUpdate("DELETE FROM " + charRel + " " +
									"WHERE CID = " + ficID + " AND " + charIDProp + " = " + propMap.get(name));
			}
		}
		
		//adding to a property
		Set<String> nameSet = new HashSet<String>();
		
		for(String name : character.get(charRel)){
			if(!propMap.keySet().contains(name)){
				nameSet.add(name);
			}
		}
		
		insertProps(nameSet, ficID, stmt, charRel, relation, IDProp, pName);
	}
	
	private void insertProps(Set<String> propSet, int charID, Statement stmt, String charRel, 
								String relation, String pID, String pName) throws SQLException {
		
		FictionalCharacter fc = new FictionalCharacter();
		fc.set(charRel, propSet);
		
		insertProps(fc, charID, stmt, charRel, relation, pID, pName);
	}
	
	private void insertProps(FictionalCharacter character, int charID, Statement stmt, String charRel, 
								String relation, String pID, String pName) throws SQLException {
		
		ResultSet rs;
		int propID;
		String propName;
		
		// inserting date
		if(charRel.equalsIgnoreCase("CharacterDateOfBirth")){
			if(!character.getDateOfBirth().isEmpty()){
				for(Pair<DateOfBirthEnum,String> p : character.getDateOfBirth()){
					
					String otherDate = p.getRight();
					otherDate = (otherDate == null) ? "NULL" : "'" + otherDate + "'";
						
					DateOfBirthEnum enumDate = p.getLeft();
					String date = (enumDate == null || enumDate == DateOfBirthEnum.NULL) ? "NULL" : "'" + enumDate + "'";
					
					stmt.executeUpdate("INSERT INTO " + charRel + "(CID, standardDate, otherDate) " +
									"VALUES(" + charID + ", " + date + ", " + otherDate + ")");					
					
				}
			}
			
			return;
		}
		
		if(!character.get(charRel).isEmpty()){
			for(String s : character.get(charRel)){
			
				String employmentTitle = null;
				propName = s;
				
				if(charRel.equalsIgnoreCase("CharacterEmployer")){
					int index = s.indexOf(':');
					
					if(index != -1){
						employmentTitle = "'" + s.substring(index+1) + "'";
						propName = s.substring(0, index);
					}
				}
				
				rs = stmt.executeQuery("SELECT " + pID + " FROM " + relation + " WHERE " + pName + " = '" + propName + "'");
				
				if(rs.next()){
					propID = rs.getInt(pID);
				}
				
				else{ 	// adding a new name in a property
				
					stmt.executeUpdate("INSERT INTO " + relation + "(" + pName + ") VALUES('" + propName + "')");
					
					rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
					rs.next();
					
					propID = rs.getInt("LAST_INSERT_ID()");
				}
				
				// adding to a character relation
				if(relation.equalsIgnoreCase("Employer")){
					stmt.executeUpdate("INSERT INTO " + charRel + "(CID, employerID, employmentTitle) " +
														"VALUES(" + charID + ", " + propID + ", " + employmentTitle + ")");
				}
				else{
					stmt.executeUpdate("INSERT INTO " + charRel + " VALUES(" + charID + ", " + propID + ")");
				}
			}
		}
	}
	
	private void updateFictionals(List<FictionalCharacter> resList, String relation, 
			String charRelation, String IDProp, String charIDProp, String nameProp) throws SQLException {
		
		Connection conn = dbManager.poolConnection();
		Statement stmt = conn.createStatement();		
		ResultSet tempRS;
		
		String orClause = "(";
		
		for(int j = 0; j < resList.size(); j++){
			orClause += ("CID = " + resList.get(j).getCharacterID());
			
			if(j+1 != resList.size()){
				orClause += " OR ";
			}
			else{
				orClause += ")";
			}
		}
		
		if(charRelation.equalsIgnoreCase("CharacterDateOfBirth")) {
			tempRS = stmt.executeQuery(
					"SELECT CID, standardDate, otherCalendarSystem, otherDate " +
					"FROM CharacterDateOfBirth "+
					"WHERE " + orClause);
			
			while(tempRS.next()){
				for(FictionalCharacter ficChar : resList){
					if(tempRS.getInt("CID") == ficChar.getCharacterID()){
						
						String dateString = tempRS.getString("otherDate");
						String calendarSystem, otherDate;
						DateOfBirthEnum dEnum;
						
						// checking for a non-trivial date
						if(tempRS.getString("otherCalendarSystem") != null){
							calendarSystem = tempRS.getString("otherCalendarSystem");
							otherDate = tempRS.getString("otherDate");
							dateString = calendarSystem + ": " + otherDate;
						}
						
						dEnum = DateOfBirthEnum.fromString(tempRS.getString("standardDate"));
						
						Pair<DateOfBirthEnum, String> datePair = new Pair<DateOfBirthEnum, String>(dEnum, dateString);
						
						ficChar.getDateOfBirth().add(datePair);
						break;
					}
				}
			}
		}
		
		else if(charRelation.equalsIgnoreCase("CharacterEmployer")){
			tempRS = stmt.executeQuery(
					"SELECT CID, " + nameProp +  ", employmentTitle " +
					"FROM " + charRelation + ", " + relation + " "+
					"WHERE " + orClause + " " +
							"AND " + charRelation + "." + charIDProp + " = " + relation + "." + IDProp);
		
			while(tempRS.next()){
				for(FictionalCharacter ficChar : resList){
					if(tempRS.getInt("CID") == ficChar.getCharacterID()){
						
						String employer = tempRS.getString(nameProp);
						String employerTitle = tempRS.getString("employmentTitle");
						if(employerTitle != null){
							employer += (": " + employerTitle);
						}
						ficChar.get(charRelation).add(employer);
						break;
					}
				}
			}
		}
		
		else{
			tempRS = stmt.executeQuery(
						"SELECT CID, " + nameProp +  " " +
						"FROM " + charRelation + ", " + relation + " "+
						"WHERE " + orClause + " " +
								"AND " + charRelation + "." + charIDProp + " = " + relation + "." + IDProp);
			
			while(tempRS.next()){
				for(FictionalCharacter ficChar : resList){
					if(tempRS.getInt("CID") == ficChar.getCharacterID()){
						ficChar.get(charRelation).add(tempRS.getString(nameProp));
						break;
					}
				}
			}
		}
		
		tempRS.close();
		stmt.close();
		dbManager.returnConnection(conn);
	}
	
	
	
	public static void main(String[] args){
		Pair<List<FictionalCharacter>, Integer> resPair = null;
		//boolean res = true;
		
		try {
			DBImpl dbImpl = new DBImpl();		
			
			//dbImpl.removeCharacter(160459);
			
			FictionalCharacter ficChar = new FictionalCharacter(160482, "Ms. Muxer");
			ficChar.setHeight(HeightEnum.BELOW_150CM);
			
			Pair<DateOfBirthEnum,String> date = new Pair<DateOfBirthEnum, String>(DateOfBirthEnum.BEFORE_1900, "T:now");
			Set<Pair<DateOfBirthEnum,String>> set0 = new HashSet<Pair<DateOfBirthEnum,String>>();
			set0.add(date);
			ficChar.setDateOfBirth(set0);
			
			Set<String> set = new HashSet<String>();
			set.add("intrator");
			set.add("battyf");			
			ficChar.setCharacterCreatedBy(set);
			
			Set<String> set1 = new HashSet<String>();
			set1.add("holed:CEO");
			//set1.add("Mr. Mux:dude");
			ficChar.setEmployers(set1);
			
			Set<String> set2 = new HashSet<String>();
			set2.add("kid Muxy");
			set2.add("kid33 Mux");
			ficChar.setChildren(set2);
			
			Set<String> set3 = new HashSet<String>();
			set3.add("dying");
			set3.add("hurt");
			ficChar.setMedicalConditions(set3);
			
			dbImpl.updateCharacter(ficChar);
			//dbImpl.createCharacter(ficChar);
			resPair = dbImpl.queryCharacter("Muxer", 1000);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if(resPair != null){
			for(FictionalCharacter fc : resPair.getLeft()){
				System.out.print(fc.getCharacterID() + "- ");
				System.out.print(fc.getName() + ": ");
				System.out.print(fc.getGender());
				System.out.print(", " + fc.getHeight());
				System.out.print(", " + fc.getCharacterCreatedBy());
				System.out.print(", " + fc.getEmployers());
				System.out.print(", " + fc.getChildren());
				System.out.print(", " + fc.getDateOfBirth());
				System.out.println(", " + fc.getMedicalConditions());
			}
			
			System.out.println("found: " + resPair.getRight());
		}
		
		//System.out.println(res);
	}

    @Override
    public void close() throws Exception {
        dbManager.close();
    }

}
